Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Public Class DAO
    Dim conn As New SqlString
    Public Function AddToCartMaster(ByVal _AddToCartMastervo As AddToCartMasterVo) As Boolean
        Dim query As String
        query = "insert into CartMaster values('" & _AddToCartMastervo.UserId & "','" & _AddToCartMastervo.TotalPrice & "','" & _AddToCartMastervo.TotalQuantity & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function AddToCart(ByVal _AddToCartvo As AddToCartVo) As Boolean
        Dim query As String
        query = "insert into CartDetail values('" & _AddToCartvo.ProductId & "','" & _AddToCartvo.UserId & "','" & _AddToCartvo.Quantity & "','" & _AddToCartvo.OriginalPrice & "','" & _AddToCartvo.DiscountedPrice & "','" & _AddToCartvo.Size & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function AddToPurchaseCart(ByVal _PurchaseCartvo As PurchaseCartVo) As Boolean
        Dim query As String
        query = "insert into PurchaseCart values('" & _PurchaseCartvo.PurchaseId & "','" & _PurchaseCartvo.ProductId & "','" & _PurchaseCartvo.Quantity & "','" & _PurchaseCartvo.Price & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function AddToPurchaseDetail(ByVal _PurchaseDetailvo As PurchaseDetailVo) As Boolean
        Dim query As String
        query = "insert into PurchaseDetail values('" & _PurchaseDetailvo.PurchaseId & "','" & _PurchaseDetailvo.ProductId & "','" & _PurchaseDetailvo.quantity & "','" & _PurchaseDetailvo.Price & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function AddToPurchaseMaster(ByVal _PurchaseMastervo As PurchaseMasterVo) As Boolean
        Dim query As String
        query = "insert into PurchaseMaster values('" & _PurchaseMastervo.PurchaseId & "','" & _PurchaseMastervo.PurchaseDate & "','" & _PurchaseMastervo.TotalQuantity & "','" & _PurchaseMastervo.TotalAmount & "','" & _PurchaseMastervo.SupplierId & "')"
        Return conn.executeinsertquery(query)
    End Function

    Public Function insertCity(ByVal _cityvo As CityVo) As Boolean
        Dim query As String
        query = "insert into city values('" & _cityvo.CountryId & "','" & _cityvo.StateId & "','" & _cityvo.CityId & "','" & _cityvo.CityName & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function insertBillingAddress(ByVal _BillingAddressvo As BillingAddressVO) As Boolean
        Dim query As String
        query = "insert into BillingAddress values('" & _BillingAddressvo.BillId & "','" & _BillingAddressvo.UserId & "','" & _BillingAddressvo.FirstName & "','" & _BillingAddressvo.LastName & "','" & _BillingAddressvo.Address & "','" & _BillingAddressvo.City & "','" & _BillingAddressvo.State & "','" & _BillingAddressvo.Country & "','" & _BillingAddressvo.PhoneNumber & "','" & _BillingAddressvo.MobileNumber & "','" & _BillingAddressvo.Zipcode & "','" & _BillingAddressvo.BillDate & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function insertShippingAddress(ByVal _ShippingAddressvo As ShippingAddressVO) As Boolean
        Dim query As String
        query = "insert into ShippingAddress values('" & _ShippingAddressvo.ShipId & "','" & _ShippingAddressvo.BillId & "','" & _ShippingAddressvo.UserId & "','" & _ShippingAddressvo.FirstName & "','" & _ShippingAddressvo.LastName & "','" & _ShippingAddressvo.Address & "','" & _ShippingAddressvo.City & "','" & _ShippingAddressvo.State & "','" & _ShippingAddressvo.Country & "','" & _ShippingAddressvo.PhoneNumber & "','" & _ShippingAddressvo.MobileNumber & "','" & _ShippingAddressvo.Zipcode & "','" & _ShippingAddressvo.ShipDate & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function insertState(ByVal _Statevo As StateVo) As Boolean
        Dim query As String
        query = "insert into State values('" & _Statevo.CountryId & "','" & _Statevo.StateId & "','" & _Statevo.StateName & "')"
        Return conn.executeInsertQuery(query)
    End Function
    Public Function insertShippingCharge(ByVal _ShippingChargevo As ShippingChargeVo) As Boolean
        Dim query As String
        query = "insert into ShippingCharge values('" & _ShippingChargevo.ShippingChargeId & "','" & _ShippingChargevo.Charge & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function insertCountry(ByVal _Countryvo As CountryVo) As Boolean
        Dim query As String
        query = "insert into Country values('" & _Countryvo.CountryId & "','" & _Countryvo.CountryName & "')"
        Return conn.executeInsertQuery(query)
    End Function
    Public Function insertCategory(ByVal _Category As CategoryVo) As Boolean
        Dim query As String
        query = "insert into Category values('" & _Category.CategoryId & "','" & _Category.CategoryName & "')"
        Return conn.executeInsertQuery(query)
    End Function
    Public Function insertSubCategory(ByVal _SubCategory As SubCategoryVo) As Boolean
        Dim query As String
        query = "insert into SubCategory values('" & _SubCategory.CategoryId & "','" & _SubCategory.SubCategoryId & "','" & _SubCategory.SubCategoryName & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function insertEmployee(ByVal _Employee As EmployeeVo) As Boolean
        Dim query As String
        query = "insert into Employee values('" & _Employee.EmployeeId & "','" & _Employee.Password & "','" & _Employee.FirstName & "','" & _Employee.LastName & "','" & _Employee.Address & "','" & _Employee.PhoneNumber & "','" & _Employee.EmailId & "','" & _Employee.Designation & "','" & _Employee.LastLogin & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function insertScheme(ByVal _Scheme As SchemeVo) As Boolean
        Dim query As String
        query = "insert into Scheme values('" & _Scheme.SchemeId & "','" & _Scheme.Discount & "','" & _Scheme.Description & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function insertProduct(ByVal _Product As ProductVo) As Boolean
        Dim query As String
        query = "insert into Product values('" & _Product.ProductId & "','" & _Product.CategoryId & "','" & _Product.SubCategoryId & "','" & _Product.Price & "','" & _Product.Brand & "','" & _Product.PhotoPath & "','" & _Product.SchemeId & "','" & _Product.TotalQuantity & "','" & _Product.AvailableQuantity & "'," & _Product.Count & ")"
        Return conn.executeinsertquery(query)
    End Function
    Public Function insertUser(ByVal _User As UserVO) As Boolean
        Dim query As String
        query = "insert into Customer values('" & _User.UserId & "','" & _User.Password & "','" & _User.FirstName & "','" & _User.LastName & "','" & _User.Address & "','" & _User.City & "','" & _User.State & "','" & _User.Country & "','" & _User.Gender & "','" & _User.BirthDate & "','" & _User.PhoneNumber & "','" & _User.MobileNumber & "','" & _User.EmailId & "','" & _User.AnniversaryDate & "','" & _User.Zipcode & "','" & _User.SecurityQuestion & "','" & _User.SecurityAnswer & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function insertBillMaster(ByVal _BillMaster As BillMasterVo) As Boolean
        Dim query As String
        query = "insert into BillMaster values('" & _BillMaster.BillId & "','" & _BillMaster.UserId & "','" & _BillMaster.BillDate & "','" & _BillMaster.Price & "','" & _BillMaster.ShippingCharge & "','" & _BillMaster.TotalQuantity & "','" & _BillMaster.CreditCardNumber & "')"
        Return conn.executeinsertquery(query)
    End Function
    'Public Function insertBillDetail(ByVal _BillDetail As BillDetailVo) As Boolean
    '    Dim query As String
    '    query = "insert into BillDetail values('" & _BillDetail.ProductId & "','" & _BillDetail.BillId & "','" & _BillDetail.Quantity & "','" & _BillDetail.Price & "')"
    '    Return conn.executeinsertquery(query)
    'End Function
    Public Function insertBillDetail(ByVal _BillDetail As BillDetailVo) As Boolean
        Dim query As String
        query = "insert into BillDetail values('" & _BillDetail.ProductId & "','" & _BillDetail.BillId & "','" & _BillDetail.Quantity & "','" & _BillDetail.Price & "','" & _BillDetail.Size & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function insertPurchaseDetail(ByVal _PurchaseDetailVo As PurchaseDetailVo) As Boolean
        Dim query As String
        query = "insert into PurchaseDetail  values('" & _PurchaseDetailVo.PurchaseId & "','" & _PurchaseDetailVo.ProductId & "','" & _PurchaseDetailVo.quantity & "','" & _PurchaseDetailVo.Price & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function insertPurchaseMaster(ByVal _PurchaseMasterVo As PurchaseMasterVo) As Boolean
        Dim query As String
        query = "insert into PurchaseMaster  values('" & _PurchaseMasterVo.PurchaseId & "','" & _PurchaseMasterVo.PurchaseDate & "','" & _PurchaseMasterVo.TotalQuantity & "','" & _PurchaseMasterVo.TotalAmount & "','" & _PurchaseMasterVo.SupplierId & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function insertCreditCard(ByVal _CreditCard As CreditCardVo) As Boolean
        Dim query As String
        query = "insert into CreditCard values('" & _CreditCard.CreditCardNumber & "','" & _CreditCard.CreditCardVerificationNumber & "','" & _CreditCard.CreditCardType & "','" & _CreditCard.HolderName & "','" & _CreditCard.ExpiryDate & "','" & _CreditCard.BankName & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function insertFeedback(ByVal _Feedbackvo As FeedbackVo) As Boolean
        Dim query As String
        query = "insert into Feedback values('" & _Feedbackvo.UserId & "','" & _Feedbackvo.FeedDate & "','" & _Feedbackvo.Feedback & "')"
        Return conn.executeinsertquery(query)
    End Function
    Public Function InsertSupplier(ByVal _Supplier As SupplierVO) As Boolean
        Dim query As String
        query = "insert into SupplierMaster values('" & _Supplier.SupplierId & "','" & _Supplier.SupplierName & "','" & _Supplier.Address & "','" & _Supplier.City & "','" & _Supplier.State & "','" & _Supplier.Country & "','" & _Supplier.PhoneNumber & "','" & _Supplier.MobileNumber & "','" & _Supplier.EmailId & "','" & _Supplier.Zipcode & "')"
        Return conn.executeinsertquery(query)
    End Function





    Public Function UpdatePurchaseCart(ByVal _ProductId As String, ByVal _Quantity As String) As Boolean
        Dim query As String
        query = "UPDATE PurchaseCart set Quantity=" & _Quantity & " where ProductId='" & _ProductId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function UpdateOnPurchase(ByVal _ProductId As String, ByVal _Quantity As String) As Boolean
        Dim query As String
        query = "UPDATE Product set AvailableQuantity=" & _Quantity & ",TotalQuantity=" & _Quantity & " where ProductId='" & _ProductId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function ModifySupplier(ByVal _Supplier As SupplierVO) As Boolean
        Dim query As String
        query = "UPDATE SupplierMaster set SupplierName='" & _Supplier.SupplierName & "',Address='" & _Supplier.Address & "',City='" & _Supplier.City & "',State='" & _Supplier.State & "',Country='" & _Supplier.Country & "',PhoneNumber='" & _Supplier.PhoneNumber & "',MobileNumber='" & _Supplier.MobileNumber & "',EmailId='" & _Supplier.EmailId & "',ZipCode='" & _Supplier.Zipcode & "' where SupplierId='" & _Supplier.SupplierId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function UpdateCount(ByVal _Count As String, ByVal _ProductId As String) As Boolean
        Dim query As String
        query = "UPDATE Product set Count=" & _Count & " where ProductId='" & _ProductId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function DeductQuantity(ByVal _ProductId As String, ByVal _Quantity As String) As Boolean
        Dim query As String
        query = "UPDATE Product set AvailableQuantity='" & _Quantity & "' where ProductId='" & _ProductId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function UpdatePassword(ByVal _Password As String, ByVal _UserId As String) As Boolean
        Dim query As String
        query = "UPDATE Customer set Password='" & _Password & "' where UserId='" & _UserId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function UpdateBillNo(ByVal _TmpBillNoVo As String) As Boolean
        Dim query As String
        query = "UPDATE TmpBillNo set BillNo='" & _TmpBillNoVo & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function UpdateShipNo(ByVal _TmpShipNoVo As String) As Boolean
        Dim query As String
        query = "UPDATE TmpShipNo set ShipNo='" & _TmpShipNoVo & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function UpdateToCart(ByVal _AddToCartVo As AddToCartVo) As Boolean
        Dim query As String
        query = "UPDATE CartDetail set Quantity='" & _AddToCartVo.Quantity & "',DiscountedPrice='" & _AddToCartVo.DiscountedPrice & "',OriginalPrice='" & _AddToCartVo.OriginalPrice & "',Size='" & _AddToCartVo.Size & "' where ProductId='" & _AddToCartVo.ProductId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function ModifyCountry(ByVal _CountryVO As CountryVo) As Boolean
        Dim query As String
        query = "UPDATE country set CountryName='" & _CountryVO.CountryName & "' where CountryId='" & _CountryVO.CountryId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function ModifyCategory(ByVal _CategoryVO As CategoryVo) As Boolean
        Dim query As String
        query = "UPDATE Category set CategoryName='" & _CategoryVO.CategoryName & "' where CategoryId='" & _CategoryVO.CategoryId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function ModifyProduct(ByVal _ProductVO As ProductVo) As Boolean
        Dim query As String
        query = "UPDATE Product set CategoryId='" & _ProductVO.CategoryId & "',SubCategoryId='" & _ProductVO.SubCategoryId & "',Price='" & _ProductVO.Price & "',Brand='" & _ProductVO.Brand & "',PhotoPath='" & _ProductVO.PhotoPath & "',SchemeId='" & _ProductVO.SchemeId & "',TotalQuantity='" & _ProductVO.TotalQuantity & "' where ProductId='" & _ProductVO.ProductId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function ModifyEmployee(ByVal _EmployeeVO As EmployeeVo) As Boolean
        Dim query As String
        query = "UPDATE Employee set Password='" & _EmployeeVO.Password & "',FirstName='" & _EmployeeVO.FirstName & "',LastName='" & _EmployeeVO.LastName & "',Address='" & _EmployeeVO.Address & "',PhoneNumber='" & _EmployeeVO.PhoneNumber & "',EmailId='" & _EmployeeVO.EmailId & "',Designation='" & _EmployeeVO.Designation & "' where EmployeeId='" & _EmployeeVO.EmployeeId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function ModifyScheme(ByVal _SchemeVO As SchemeVo) As Boolean
        Dim query As String
        query = "UPDATE Scheme set Discount='" & _SchemeVO.Discount & "',DIscription='" & _SchemeVO.Description & "' where SchemeId='" & _SchemeVO.SchemeId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function ModifyShippingCharge(ByVal _ShippingChargeVO As ShippingChargeVo) As Boolean
        Dim query As String
        query = "UPDATE ShippingCharge set Charge='" & _ShippingChargeVO.Charge & "' where ShippingChargeId='" & _ShippingChargeVO.ShippingChargeId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function ModifySubCategory(ByVal _SubCategoryVO As SubCategoryVo) As Boolean
        Dim query As String
        query = "UPDATE SubCategory set SubCategoryName='" & _SubCategoryVO.SubCategoryName & "',CategoryId='" & _SubCategoryVO.CategoryId & "' where SubCategoryId='" & _SubCategoryVO.SubCategoryId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function ModifyState(ByVal _StateVO As StateVo) As Boolean
        Dim query As String
        query = "UPDATE State set StateName='" & _StateVO.StateName & "',CountryId='" & _StateVO.CountryId & "' where StateId='" & _StateVO.StateId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function ModifyCity(ByVal _CityVO As CityVo) As Boolean
        Dim query As String
        query = "UPDATE City set CityName='" & _CityVO.CityName & "',CountryId='" & _CityVO.CountryId & "',StateId='" & _CityVO.StateId & "' where CityId='" & _CityVO.CityId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function UpdateLastLogin(ByVal EmployeeId As String) As Boolean
        Dim query As String
        query = "UPDATE Employee set LastLogin='" & Now.ToString & "' where EmployeeId='" & EmployeeId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function GetLastLogin(ByVal _EmployeeId As String) As DataTable
        Dim query As String
        query = "select EmployeeId,FirstName,EmailId,Designation,LastLogin from Employee where EmployeeId='" & _EmployeeId & "'"
        Return conn.executeselectquery(query)
    End Function
    'Public Function getPurchaseCart(ByVal _PurchaseId As String) As DataTable
    '    Dim query As String
    '    query = "select * from PurchaseCart where PurchaseId='" & _PurchaseId & "'"
    '    Return conn.executeselectquery(query)
    'End Function
    Public Function getcart() As DataTable
        Dim query As String
        query = "select a.Brand,b.Quantity from Product as a,CartDetail as b where a.ProductId=b.ProductId group by Brand,b.Quantity"
        Return conn.executeselectquery(query)
    End Function
    Public Function getTotalQuantity() As DataTable
        Dim query As String
        query = "select sum(Quantity) from CartDetail"
        Return conn.executeselectquery(query)
    End Function
    Public Function getPassword(ByVal _UserId As String) As DataTable
        Dim query As String
        query = "select Password from Customer where UserId='" & _UserId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getCount(ByVal _ProductId As String) As DataTable
        Dim query As String
        query = "select Count from Product where ProductId='" & _ProductId & "'"
        Return conn.executeselectquery(query)
    End Function





    Public Function DeletePurchaseCart() As Boolean
        Dim query As String
        query = "delete from PurchaseCart"
        Return conn.executeDeleteQuery(query)
    End Function
    Public Function DeleteSupplier(ByVal _SupplierId As String) As Boolean
        Dim query As String
        query = "delete from SupplierMaster where SupplierId='" & _SupplierId & "'"
        Return conn.executeDeleteQuery(query)
    End Function
    Public Function DeleteCartMaster() As Boolean
        Dim query As String
        query = "delete from CartMaster"
        Return conn.executeDeleteQuery(query)
    End Function
    Public Function DeleteCartDetail() As Boolean
        Dim query As String
        query = "delete from CartDetail"
        Return conn.executeDeleteQuery(query)
    End Function
    Public Function DeleteFromCart(ByVal _UserId As String, ByVal _ProductId As String) As Boolean
        Dim query As String
        query = "delete from CartDetail where UserId='" & _UserId & "' and ProductId='" & _ProductId & "'"
        Return conn.executeDeleteQuery(query)
    End Function
    Function DeleteFromPurchaseCart(ByVal _PurchaseId As String, ByVal _ProductId As String) As Boolean
        Dim query As String
        query = "delete from PurchaseCart where PurchaseId='" & _PurchaseId & "' and ProductId='" & _ProductId & "'"
        Return conn.executeDeleteQuery(query)
    End Function
    Function DeleteFromPurchaseCart(ByVal _PurchaseId As String) As Boolean
        Dim query As String
        query = "delete from PurcahseCart where PurchaseId='" & _PurchaseId & "'"
        Return conn.executeDeleteQuery(query)
    End Function
    Public Function DeleteCategory(ByVal _CategoryId As String) As Boolean
        Dim query As String
        query = "delete from Category where CategoryId='" & _CategoryId & "'"
        Return conn.executeDeleteQuery(query)
    End Function
    Public Function DeleteScheme(ByVal _SchemeId As String) As Boolean
        Dim query As String
        query = "delete from Scheme where SchemeId='" & _SchemeId & "'"
        Return conn.executeDeleteQuery(query)
    End Function
    Public Function DeleteCity(ByVal _CityId As String) As Boolean
        Dim query As String
        query = "delete from City where CityId='" & _CityId & "'"
        Return conn.executeDeleteQuery(query)
    End Function
    Public Function DeleteShippingCharge(ByVal _ShippingChargeId As String) As Boolean
        Dim query As String
        query = "delete from ShippingCharge where ShippingChargeId='" & _ShippingChargeId & "'"
        Return conn.executeDeleteQuery(query)
    End Function
    Public Function DeleteState(ByVal _StateId As String) As Boolean
        Dim query As String
        query = "delete from State where StateId='" & _StateId & "'"
        Return conn.executeDeleteQuery(query)
    End Function
    Public Function DeleteSubCategory(ByVal _SubCategoryId As String) As Boolean
        Dim query As String
        query = "delete from SubCategory where SubCategoryId='" & _SubCategoryId & "'"
        Return conn.executeDeleteQuery(query)
    End Function
    Public Function DeleteCountry(ByVal _CountryId As String) As Boolean
        Dim query As String
        query = "delete from Country where CountryId='" & _CountryId & "'"
        Return conn.executeDeleteQuery(query)
    End Function
    Public Function DeleteProduct(ByVal _ProductId As String) As Boolean
        Dim query As String
        query = "delete from Product where ProductId='" & _ProductId & "'"
        Return conn.executeDeleteQuery(query)
    End Function
    Public Function DeleteEmployee(ByVal _EmployeeId As String) As Boolean
        Dim query As String
        query = "delete from Employee where EmployeeId='" & _EmployeeId & "'"
        Return conn.executeDeleteQuery(query)
    End Function




    Public Function CheckProductInPurchaseCart(ByVal _ProductId As String) As DataTable
        Dim query As String
        query = "select Quantity from PurchaseCart where ProductId='" & _ProductId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getQuantity(ByVal _ProductId As String) As DataTable
        Dim query As String
        query = "select AvailableQuantity from Product where ProductId='" & _ProductId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getShipNo() As DataTable
        Dim query As String
        query = "select * from tmpShipNo"
        Return conn.executeselectquery(query)
    End Function
    Public Function getBillNo() As DataTable
        Dim query As String
        query = "select * from tmpBillNo"
        Return conn.executeselectquery(query)
    End Function
    Public Function getTotal(ByVal _UserId As String) As DataTable
        Dim query As String
        query = "select Sum(Quantity),sum(DiscountedPrice),sum(OriginalPrice) from CartDetail where UserId='" & _UserId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getPurchaseTotal(ByVal _PurchaseId As String) As DataTable
        Dim query As String
        query = "select Sum(Quantity),sum(Price) from PurchaseDetail where PurchaseId=" & _PurchaseId & ""
        Return conn.executeselectquery(query)
    End Function
    Public Function getQuantity(ByVal _ProductId As String, ByVal _UserId As String) As DataTable
        Dim query As String
        query = "select Quantity from CartDetail where ProductId='" & _ProductId & "' and UserId='" & _UserId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function CheckProductInCart(ByVal _ProductId As String, ByVal _UserId As String) As DataTable
        Dim query As String
        query = "select * from CartDetail where ProductId='" & _ProductId & "' and UserId='" & _UserId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getCartByProduct(ByVal _ProductId As String, ByVal _UserId As String) As DataTable
        Dim query As String
        query = "select CartDetail.ProductId,Quantity,Price,DiscountedPrice,PhotoPath,Brand,Product.SchemeId,Discount,AvailableQuantity,Size from CartDetail,Product,Scheme where CartDetail.ProductId='" & _ProductId & "' and UserId='" & _UserId & "' and CartDetail.ProductId=Product.ProductId and Product.SchemeId=Scheme.SchemeId"
        Return conn.executeselectquery(query)
    End Function
  
    Public Function getCart(ByVal _UserId As String) As DataTable
        Dim query As String
        query = "select CartDetail.ProductId,CartDetail.Quantity,OriginalPrice,DiscountedPrice,PhotoPath,Size from CartDetail,Product where UserId='" & _UserId & "' and CartDetail.ProductId=Product.ProductId"
        Return conn.executeselectquery(query)
    End Function
    Public Function getPurchaseCart(ByVal _PurchaseId As String) As DataTable
        Dim query As String
        query = "select * from PurchaseCart where PurchaseId='" & _PurchaseId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getFeeds() As DataTable
        Dim query As String
        query = "select * from Feedback"
        Return conn.executeselectquery(query)
    End Function
    Public Function getBillDate(ByVal _UserId As String) As DataTable
        Dim query As String
        query = "select BillId,BillDate from BillMaster where BillId in (select Max(BillId) from BillMaster where UserId='" & _UserId & "')"
        Return conn.executeselectquery(query)
    End Function
    Public Function CheckUser(ByVal _UserId As String, ByVal _Password As String) As DataTable
        Dim query As String
        query = "select UserId,Password from Customer where UserId='" & _UserId & "'and password='" & _Password & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function CheckEmployee(ByVal _EmployeeId As String, ByVal _Password As String) As DataTable
        Dim query As String
        query = "select EmployeeId,Password from Employee where EmployeeId='" & _EmployeeId & "'and password='" & _Password & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getDiscount(ByVal _SchemeId As String) As DataTable
        Dim query As String
        query = "select Discount from Scheme where SchemeId='" & _SchemeId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getDescription(ByVal _SchemeId As String) As DataTable
        Dim query As String
        query = "select * from Scheme where SchemeId='" & _SchemeId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getShippingCharge(ByVal _ShippingChargeId As String) As DataTable
        Dim query As String
        query = "select * from ShippingCharge where ShippingChargeId='" & _ShippingChargeId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getShippingChargeById(ByVal _ShippingChargeId As String) As DataTable
        Dim query As String
        query = "select * from ShippingCharge where ShippingChargeId='" & _ShippingChargeId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getShippingChargeDetails() As DataTable
        Dim query As String
        query = "select * from ShippingCharge"
        Return conn.executeselectquery(query)
    End Function
    Public Function getSubCategoryById(ByVal _SubCategoryId As String) As DataTable
        Dim query As String
        query = "select * from SubCategory where SubCategoryId='" & _SubCategoryId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getSubCategoryDetails() As DataTable
        Dim query As String
        query = "select * from SubCategory"
        Return conn.executeselectquery(query)
    End Function
    Public Function selectCategoryId(ByVal _CategoryId As String) As DataTable
        Dim query As String
        query = "select CategoryId from Category where CategoryId='" & _CategoryId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getcountry() As DataTable
        Dim query As String
        query = "select * from Country"
        Return conn.executeselectquery(query)
    End Function
    Public Function getCountryByName(ByVal _CountryName As String) As DataTable
        Dim query As String
        query = "select CountryId from Country where CountryName='" & _CountryName & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getCountryById(ByVal _CountryId As String) As DataTable
        Dim query As String
        query = "select * from Country where CountryId='" & _CountryId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getstate() As DataTable
        Dim query As String
        query = "select * from state"
        Return conn.executeselectquery(query)
    End Function
    Public Function getstate(ByVal _CountryId As String) As DataTable
        Dim query As String
        query = "select * from State where CountryId ='" & _CountryId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getCity(ByVal _StateId As String) As DataTable
        Dim query As String
        query = "select * from City where StateId ='" & _StateId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getcountryDetails() As DataTable
        Dim query As String
        query = "select * from Country"
        Return conn.executeselectquery(query)
    End Function
    Public Function getCategoryById(ByVal _CategoryId As String) As DataTable
        Dim query As String
        query = "select * from Category where CategoryId='" & _CategoryId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getCategoryDetails() As DataTable
        Dim query As String
        query = "select * from Category"
        Return conn.executeselectquery(query)
    End Function
    Public Function getSupplierById(ByVal _SupplierId As String) As DataTable
        Dim query As String
        query = "select * from SupplierMaster where SupplierId='" & _SupplierId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getPurchaseId() As DataTable
        Dim query As String
        query = "select Max(PurchaseId) from PurchaseMaster"
        Return conn.executeselectquery(query)
    End Function
    Public Function getProductById(ByVal _ProductId As String) As DataTable
        Dim query As String
        query = "select * from Product where ProductId='" & _ProductId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getPriceByProductId(ByVal _ProductId As String) As DataTable
        Dim query As String
        query = "select Price,Photopath from Product where ProductId='" & _ProductId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getProductByDiscount(ByVal min As String, ByVal max As String) As DataTable
        Dim query As String
        query = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Discount desc) AS rownum, ProductId,PhotoPath, Price,Brand,Discount FROM Product,Scheme where Product.SchemeId=Scheme.SchemeId) AS p1 WHERE  rownum between " & Val(min) & " and " & Val(max) & ""
        'query = "select a.ProductId,a.Brand,a.Price,a.PhotoPath,b.Discount from Product as a,Scheme as b where a.SchemeId=b.SchemeId order by a.SchemeId"
        Return conn.executeselectquery(query)
    End Function
    Public Function getProductByPrice(ByVal min As String, ByVal max As String) As DataTable
        Dim query As String
        query = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Price desc) AS rownum, ProductId,PhotoPath, Price,Brand,Discount FROM Product,Scheme where Product.SchemeId=Scheme.SchemeId) AS p1 WHERE  rownum between " & Val(min) & " and " & Val(max) & ""
        '"select a.ProductId,a.Brand,a.Price,a.PhotoPath,b.Discount from Product as a,Scheme as b where a.SchemeId=b.SchemeId order by a.Price"
        Return conn.executeselectquery(query)
    End Function
    Public Function brand() As DataTable
        Dim query As String
        query = "select distinct(Brand),count(brand) from Product group by brand"
        Return conn.executeselectquery(query)
    End Function
    Public Function getProductByBrand(ByVal min As String, ByVal max As String) As DataTable
        Dim query As String
        query = "SELECT * FROM (SELECT ROW_NUMBER()over(order by brand) AS rownum, ProductId,PhotoPath, Price,Brand,Discount FROM Product,Scheme where Product.SchemeId=Scheme.SchemeId) AS p1 WHERE  rownum between " & Val(min) & " and " & Val(max) & ""
        'query = "select a.ProductId,a.Brand,a.Price,a.PhotoPath,b.Discount from Product as a,Scheme as b where a.SchemeId=b.SchemeId order by a.Brand"
        Return conn.executeselectquery(query)
    End Function
    Public Function getProductByBrand(ByVal min As String, ByVal max As String, ByVal _brand As String) As DataTable
        Dim query As String
        query = "SELECT * FROM (SELECT ROW_NUMBER()OVER(order by Brand)  AS rownum, ProductId,PhotoPath, Price FROM Product where Brand='" & _brand & "') AS p1 WHERE  rownum between " & Val(min) & " and " & Val(max) & ""
        'query = "select ProductId,PhotoPath,Price from Product where Brand='" & brand & "'"
        Return conn.executeselectquery(query)
    End Function
    'Public Function getSubCatWiseProduct(ByVal _SubCatId As String) As DataTable
    '    Dim query As String
    '    query = "select a.ProductId,a.Brand,a.Price,a.PhotoPath,b.Discount from Product as a,Scheme as b where a.SchemeId=b.SchemeId and SubCategoryId='" & _SubCatId & "'"
    '    Return conn.executeselectquery(query)
    'End Function
    Public Function getProductDetails() As DataTable
        Dim query As String
        query = "select a.ProductId,a.Brand,a.Price,a.PhotoPath,b.Discount from Product as a,Scheme as b where a.SchemeId=b.SchemeId"
        Return conn.executeselectquery(query)
    End Function
    Public Function getSubCatWiseProduct(ByVal min As String, ByVal max As String, ByVal _SubCatId As String) As DataTable
        Dim query As String
        query = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductId) AS rownum, ProductId,PhotoPath, Price,Brand,Discount FROM Product,Scheme where Product.SchemeId=Scheme.SchemeId and Product.SubCategoryId='" & _SubCatId & "') AS p1 WHERE  rownum between " & Val(min) & " and " & Val(max) & ""
        Return conn.executeselectquery(query)
    End Function
    Public Function getWomenProducts(ByVal min As String, ByVal max As String) As DataTable
        Dim query As String
        query = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductId) AS rownum, ProductId,PhotoPath, Price,Brand,Discount FROM Product,Scheme where Product.SchemeId=Scheme.SchemeId and Product.CategoryId='W') AS p1 WHERE  rownum between " & Val(min) & " and " & Val(max) & ""
        Return conn.executeselectquery(query)
    End Function
    Public Function getMenProducts(ByVal min As String, ByVal max As String) As DataTable
        Dim query As String
        query = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductId) AS rownum, ProductId,PhotoPath, Price,Brand,Discount FROM Product,Scheme where Product.SchemeId=Scheme.SchemeId and Product.CategoryId='M') AS p1 WHERE  rownum between " & Val(min) & " and " & Val(max) & ""
        Return conn.executeselectquery(query)
        End Function
    
    Public Function getKidsProducts(ByVal min As String, ByVal max As String) As DataTable
        Dim query As String
        query = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductId) AS rownum, ProductId,PhotoPath, Price,Brand,Discount FROM Product,Scheme where Product.SchemeId=Scheme.SchemeId and Product.CategoryId='K') AS p1 WHERE  rownum between " & Val(min) & " and " & Val(max) & ""
        Return conn.executeselectquery(query)
    End Function
    
    Public Function getProduct() As DataTable
        Dim query As String
        query = "select * from Product"
        Return conn.executeselectquery(query)
    End Function
    Public Function getScheme() As DataTable
        Dim query As String
        query = "select * from Scheme"
        Return conn.executeselectquery(query)
    End Function
    Public Function getsubcategory(ByVal _categoryId As String) As DataTable
        Dim query As String
        query = "select * from SubCategory where CategoryId ='" & _categoryId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getcategory() As DataTable
        Dim query As String
        query = "select * from Category"
        Return conn.executeselectquery(query)
    End Function
    Public Function getEmployeeById(ByVal _EmployeeId As String) As DataTable
        Dim query As String
        query = "select * from Employee where EmployeeId='" & _EmployeeId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getEmployeeDetails() As DataTable
        Dim query As String
        query = "select * from Employee"
        Return conn.executeselectquery(query)
    End Function
    Public Function getSupplierDetails() As DataTable
        Dim query As String
        query = "select * from SupplierMaster"
        Return conn.executeselectquery(query)
    End Function

    Public Function getSchemeById(ByVal _SchemeId As String) As DataTable
        Dim query As String
        query = "select * from Scheme where SchemeId='" & _SchemeId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getSchemeDetails() As DataTable
        Dim query As String
        query = "select * from Scheme"
        Return conn.executeselectquery(query)
    End Function
    Public Function getCityById(ByVal _CityId As String) As DataTable
        Dim query As String
        query = "select * from City where CityId='" & _CityId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getCityDetails() As DataTable
        Dim query As String
        query = "select * from City"
        Return conn.executeselectquery(query)
    End Function
    Public Function getStateById(ByVal _StateId As String) As DataTable
        Dim query As String
        query = "select * from State where StateId='" & _StateId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getStateDetails() As DataTable
        Dim query As String
        query = "select * from State"
        Return conn.executeselectquery(query)
    End Function
    Public Function getUserDetail(ByVal _UserId As String) As DataTable
        Dim query As String
        query = "select * from Customer where UserId='" & _UserId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getSecurityQuestion(ByVal _UserId As String) As DataTable
        Dim query As String
        query = "select SecurityQuestion,SecurityAnswer from Customer where UserId='" & _UserId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getBillingAddressDetail(ByVal _BillId As String) As DataTable
        Dim query As String
        query = "select * from BillingAddress where BillId='" & _BillId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function extracthomeproduct(ByVal min As String, ByVal max As String) As DataTable
        Dim query As String
        query = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductId) AS rownum, ProductId,PhotoPath, Price,Brand,Discount FROM Product,Scheme where Product.SchemeId=Scheme.SchemeId) AS p1 WHERE  rownum between " & Val(min) & " and " & Val(max) & ""
        Return conn.executeselectquery(query)
    End Function
    'Counting Total Product
    Public Function counttotproductBrand(ByVal _Brand As String) As Integer
        Dim query As String
        query = "select count(*) from Product where Brand='" & _Brand & "'"
        Return conn.executeCount(query)
    End Function
    Public Function counttotproduct() As Integer
        Dim query As String
        query = "select count(*) from Product"
        Return conn.executeCount(query)
    End Function
    Public Function counttotSubCatProduct(ByVal _SubCatId As String) As Integer
        Dim query As String
        query = "select count(*) from Product where subCategoryId='" & _SubCatId & "'"
        Return conn.executeCount(query)
    End Function
    Public Function counttotproductCategory(ByVal _Cat As String) As Integer
        Dim query As String
        query = "select count(*) from Product where CategoryId='" & _Cat & "'"
        Return conn.executeCount(query)
    End Function
    Public Function Topbrand(ByVal min As String, ByVal max As String) As DataTable
        Dim query As String
        query = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Count desc) AS rownum, ProductId,PhotoPath, Price,Brand,Discount FROM Product,Scheme where Product.SchemeId=Scheme.SchemeId) AS p1 WHERE  rownum between " & Val(min) & " and " & Val(max) & ""
        '"select ProductId,Brand,PhotoPath,Price,Count,Discount FROM Product,Scheme where Product.SchemeId=Scheme.SchemeId Order by Count desc"
        Return conn.executeselectquery(query)
    End Function
    Public Function UpdateQuantityInCart(ByVal _UserId As String, ByVal _ProductId As String, ByVal _Quantity As String) As Boolean
        Dim query As String
        query = "UPDATE CartDetail set Quantity=" & _Quantity & " where ProductId='" & _ProductId & "' and UserId='" & _UserId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function UpdateQuantityInPurchaseCart(ByVal _PurchaseId As String, ByVal _ProductId As String, ByVal _Quantity As String) As Boolean
        Dim query As String
        query = "UPDATE PurchaseCart set Quantity=" & _Quantity & " where ProductId='" & _ProductId & "' and  PurchaseId='" & _PurchaseId & "'"
        Return conn.executeinsertquery(query)
    End Function
    Public Function getPurchaseCartDetails(ByVal _ProductId As String) As DataTable
        Dim query As String
        query = "select PurchaseId,Quantity,Price from PurchaseCart where ProductId='" & _ProductId & "'"
        Return conn.executeselectquery(query)
    End Function
    Public Function getCartDetails(ByVal _UserId As String) As DataTable
        Dim query As String
        query = "select Quantity,DiscountedPrice from CartDetail where UserId='" & _UserId & "'"
        Return conn.executeselectquery(query)
    End Function
End Class
